package com.hongdee.crm.kpi.service.impl;

import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.kpi.dao.IRealMaintainBackDao;
import com.hongdee.crm.kpi.service.IRealMaintainBackService;
import org.ietf.jgss.Oid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.text.SimpleDateFormat;
import java.util.*;

import static com.hongdee.crm.Constant.SA_ROLE_ID;

/**
 * Created by zhangkai on 2017/7/22.
 */
@Service
public class RealMaintainBackServiceImpl implements IRealMaintainBackService {
    @Autowired
    IRealMaintainBackDao iRealMaintainBackDao;

    /**
     * 1.基盘月 指定月份的进厂，默认为当前月向前推6个月
     * 2.考核期 指定回厂的截止日期，默认为当天
     * 3.
     * SA：服务顾问
     * 基盘首保车辆数：基盘月内做过首保的车辆
     * 基盘二保车辆数：基盘月内做过二保的车辆
     * 基盘定保车辆数：基盘月内做过定保的车辆
     * 首保回厂数：在基盘月首保之后，在考核期之前做过保养的车辆数
     * 二保回厂数：在基盘月二保之后，在考核期之前做过保养的车辆数
     * 定保回厂数：在基盘月定保之后，在考核期之前做过保养的车辆数
     * 首保回厂率：首保回厂数/首保车辆数,保留两位小数
     * 二保回厂率：二保回厂数/二保车辆数,保留两位小数
     * 定保回厂率：定保回厂数/定保车辆数,保留两位小数
     * 4. 按照SA分组，未分配
     *
     * @param baseMon   基盘月
     * @param mDate     考核期
     * @param companyId 用户ID
     * @return
     * @throws Exception
     */
    @Override
    public List byBack(String uid, String baseMon, String mDate, String companyId) throws Exception {

        if (StringUtils.isEmpty(baseMon)) {
            Long todayL = DateUtils.getNowTime();
            Long preSixmL = DateUtils.addMonthLongTime(todayL, -6);
            baseMon = DateUtils.formatLongTime(preSixmL, "YYYY-MM");
        }
        if (StringUtils.isEmpty(mDate)) {
            mDate = DateUtils.getTimeStr("yyyy-MM-dd");
        }
        String baseDate = baseMon + "-01";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date dt = sdf.parse(baseDate);
        Calendar cl = Calendar.getInstance();
        cl.setTime(dt);
        cl.set(Calendar.DAY_OF_MONTH, 1);
        cl.add(Calendar.MONTH, 1);
        String nextMonthDate = sdf.format(cl.getTime());

        String curCompanyId = TokenUtil.getCompanyId(uid);

        List params = new ArrayList();
        StringBuffer bySQL = new StringBuffer();
        bySQL.append("SELECT  ")
                .append(" temp.u_name,  ")
                .append(" temp.id,  ")
                .append(" count(  ")
                .append("  ")
                .append("  IF (  ")
                .append("   temp.wx_type = '首保',  ")
                .append("   TRUE,  ")
                .append("   NULL  ")
                .append("  )  ")
                .append(" ) AS sb,  ")
                .append(" count(  ")
                .append("  ")
                .append("  IF (  ")
                .append("   temp.wx_type = '首保'  ")
                .append("   AND temp.next_date IS NOT NULL,  ")
                .append("   TRUE,  ")
                .append("   NULL  ")
                .append("  )  ")
                .append(" ) AS sbhc,  ")
                .append(" count(  ")
                .append("  ").append("  IF (  ").append("   temp.wx_type = '二保',  ").append("   TRUE,  ")
                .append("   NULL  ").append("  )  ").append(" ) AS eb,  ").append(" count(  ").append("  ")
                .append("  IF (  ").append("   temp.wx_type = '二保'  ").append("   AND temp.next_date IS NOT NULL,  ")
                .append("   TRUE,  ").append("   NULL  ").append("  )  ").append(" ) AS ebhc,  ").append(" count(  ")
                .append("  ").append("  IF (  ").append("   temp.wx_type = '定保',  ").append("   TRUE,  ")
                .append("   NULL  ").append("  )  ").append(" ) AS db,  ").append(" count(  ").append("  ")
                .append("  IF (  ").append("   temp.wx_type = '定保'  ").append("   AND temp.next_date IS NOT NULL,  ")
                .append("   TRUE,  ").append("   NULL  ").append("  )  ").append(" ) AS dbhc  ").append("FROM  ")
                .append(" (  ").append("  SELECT  ").append("   o.company_id,  ").append("   o.vin,  ") // 改为去除distinct 、append("  SELECT DISTINCT  ")
                .append("   wx_type,  ").append("   (  ").append("    SELECT  ").append("     min(  ")
                .append("      FROM_UNIXTIME(  ").append("       o1.balance_date / 1000,  ").append("  '%Y-%m-%d'  ")
                .append("      )  ").append("     )  ").append("    FROM  ").append("     t_crm_car_workorder o1  ")
                .append("    WHERE  ").append("     o.vin = o1.vin  ").append("    AND o1.company_id = o.company_id  ")
                .append("    AND o1.wx_type IN ('首保', '二保', '定保')  ").append("    AND FROM_UNIXTIME(  ")
                .append("     o1.balance_date / 1000,  ").append("     '%Y-%m-%d'  ")
                .append(" ) BETWEEN ? AND ?  ");
        params.add(nextMonthDate);
        params.add(mDate);
        bySQL.append("   ) AS next_date,  ").append("   if(u.u_name is null ,'未分配',u.u_name) as u_name,  ")
                .append("   if (u.id is null,'未分配',u.id) as id   ").append("  FROM  ")
    /**
     * 2017年10月11日10:26:11
     * 修改代码 任务数改为车辆数（车辆去重）
     *
     * */
                .append("  ( SELECT * FROM t_crm_car_workorder WHERE company_id = ? AND FROM_UNIXTIME( balance_date / 1000, '%Y-%m' ) = ?" +
                        " AND wx_type IN ('首保', '二保', '定保') GROUP BY vin " +
                        " ) o  ");
        params.add(companyId);
        params.add(baseMon);

                bySQL.append("  LEFT JOIN t_crm_car_conf_detail dl ON o.vin = dl.vin  ")
                .append("  AND dl.role_id = '" + SA_ROLE_ID + "'  ")
                .append("  AND o.company_id = dl.company_id  ").append("  LEFT JOIN t_bc_user u ON dl.u_id = u.id  ")
                .append("  WHERE  ").append("   o.company_id = ?  ");
        params.add(companyId);
        if (!TokenUtil.isCompanyAdmin(uid) && curCompanyId.equals(companyId)) {
            bySQL.append(" AND dl.u_id = ? ");
            params.add(uid);
        }
        bySQL.append("  AND FROM_UNIXTIME(o.balance_date / 1000, '%Y-%m') = ?  ");
        params.add(baseMon);
        bySQL.append("  AND o.wx_type IN ('首保', '二保', '定保')  ").append(" ) temp  ");


        bySQL.append(" GROUP BY  ")
                .append(" temp.id,  ").append(" temp.u_name  ");
        /*分拆合计**/

        bySQL.append("UNION ALL  ").append(" SELECT  ")
                .append("  temp.u_name,  ").append("  temp.id,  ").append("  count(  ").append("  ")
                .append("   IF (  ").append("    temp.wx_type = '首保',  ").append("    TRUE,  ")
                .append("    NULL  ").append("   )  ").append("  ) AS sb,  ").append("  count(  ").append("  ")
                .append("   IF (  ").append("    temp.wx_type = '首保'  ")
                .append("    AND temp.next_date IS NOT NULL,  ").append("    TRUE,  ").append("    NULL  ")
                .append("   )  ").append("  ) AS sbhc,  ").append("  count(  ").append("  ").append("   IF (  ")
                .append("    temp.wx_type = '二保',  ").append("    TRUE,  ").append("    NULL  ").append("   )  ")
                .append("  ) AS eb,  ").append("  count(  ").append("  ").append("   IF (  ")
                .append("    temp.wx_type = '二保'  ").append("    AND temp.next_date IS NOT NULL,  ")
                .append("    TRUE,  ").append("    NULL  ").append("   )  ").append("  ) AS ebhc,  ")
                .append("  count(  ").append("  ").append("   IF (  ").append("    temp.wx_type = '定保',  ")
                .append("    TRUE,  ").append("    NULL  ").append("   )  ").append("  ) AS db,  ")
                .append("  count(  ").append("  ").append("   IF (  ").append("    temp.wx_type = '定保'  ")
                .append("    AND temp.next_date IS NOT NULL,  ").append("    TRUE,  ").append("    NULL  ")
                .append("   )  ").append("  ) AS dbhc  ").append(" FROM  ").append("  (  ").append("   SELECT  ") // 改为去除distinct
                .append("    o.company_id,  ").append("    o.vin,  ").append("    wx_type,  ").append("    (  ")
                .append("     SELECT  ").append("      min(  ").append("       FROM_UNIXTIME(  ")
                .append("        o1.balance_date / 1000,  ").append("        '%Y-%m-%d'  ").append("       )  ")
                .append("      )  ").append("     FROM  ").append("      t_crm_car_workorder o1  ").append("     WHERE  ")
                .append("      o.vin = o1.vin  ").append("     AND o1.company_id = o.company_id  ")
                .append("     AND o1.wx_type IN ('首保', '二保', '定保')  ").append("     AND FROM_UNIXTIME(  ")
                .append("      o1.balance_date / 1000,  ").append("      '%Y-%m-%d'  ")
                .append("     ) BETWEEN ?      AND ?  ").append("    ) AS next_date,  ");
        params.add(nextMonthDate);
        params.add(mDate);
        bySQL.append("    '合计' u_name,  ").append("    '合计' id  ").append("   FROM  ")
                //.append("    t_crm_car_workorder o  ")
    /**
     * 2017年10月11日10:26:11
     * 修改代码 任务数改为车辆数（车辆去重）
     *
     * */
                .append("  ( SELECT * FROM t_crm_car_workorder WHERE company_id = ? AND FROM_UNIXTIME( balance_date / 1000, '%Y-%m' ) = ?" +
                        " AND wx_type IN ('首保', '二保', '定保') GROUP BY vin " +
                        " ) o  ");
            params.add(companyId);
            params.add(baseMon);

                bySQL.append(" LEFT  JOIN t_crm_car_conf_detail dl ON o.vin = dl.vin  ").append("   AND dl.role_id = '" + SA_ROLE_ID + "'  ")
                .append("   AND o.company_id = dl.company_id  ").append("   LEFT JOIN t_bc_user u ON dl.u_id = u.id  ")
                .append("   WHERE  ").append("    o.company_id = ?  ");
        params.add(companyId);
        if (!TokenUtil.isCompanyAdmin(uid) && curCompanyId.equals(companyId)) {
            bySQL.append(" AND dl.u_id = ? ");
            params.add(uid);
        }
        bySQL
                .append("   AND FROM_UNIXTIME(o.balance_date / 1000, '%Y-%m') = ?  ");
        params.add(baseMon);
        bySQL
                .append("   AND o.wx_type IN ('首保', '二保', '定保')  ").append("  ) temp  ").append(" GROUP BY  ")
                .append("  temp.id,  ").append("  temp.u_name ");

        return this.iRealMaintainBackDao.list(bySQL.toString(), params.toArray());
    }

    /**
     * 基盘回站明细
     *
     * @param baseMon
     * @param mDate
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public Map byBackDetail(String companyId, String baseMon, String mDate, String uid, String id, String type, String start, String limit) throws Exception {
        if (StringUtils.isEmpty(baseMon)) {
            Long todayL = DateUtils.getNowTime();
            Long preSixmL = DateUtils.addMonthLongTime(todayL, -6);
            baseMon = DateUtils.formatLongTime(preSixmL, "YYYY-MM");
        }
        if (StringUtils.isEmpty(mDate)) {
            mDate = DateUtils.getTimeStr("YYYY-MM-dd");
        }
        String baseDate = baseMon + "-01";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date dt = sdf.parse(baseDate);
        Calendar cl = Calendar.getInstance();
        cl.setTime(dt);
        cl.set(Calendar.DAY_OF_MONTH, 1);
        cl.add(Calendar.MONTH, 1);
        String nextMonthDate = sdf.format(cl.getTime());

        String curCompanyId = TokenUtil.getCompanyId(uid);
        List params = new ArrayList();
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" SELECT  ").append(" temp2.*, c.* ").append("FROM  ").append(" (  ").append("  SELECT  ") // 改为去除distinct
                .append("  temp.wx_type,temp.u_name,temp.u_id ,temp.vin,temp.company_id ").append("  FROM  ").append("   (  ").append("    SELECT  ")// 改为去除distinct
                .append("     o.company_id,  ").append("     o.vin,  ").append("     wx_type,  ").append("     (  ")
                .append("      SELECT  ").append("       min(  ").append("        FROM_UNIXTIME(  ")
                .append("         o1.balance_date / 1000,  ").append("         '%Y-%m-%d'  ").append("        )  ")
                .append("       )  ").append("      FROM  ").append("       t_crm_car_workorder o1  ")
                .append("      WHERE  ").append("       o.vin = o1.vin  ").append("      AND o1.company_id = o.company_id  ")
                .append("      AND o1.wx_type IN ('首保', '二保', '定保')  ").append("      AND FROM_UNIXTIME(  ")
                .append("       o1.balance_date / 1000,  ").append("       '%Y-%m-%d'  ").append("      ) BETWEEN ?  ")
                .append("      AND ? ");
        params.add(nextMonthDate);
        params.add(mDate);

        sqlBuf.append("     ) AS next_date,  ").append("  ").append("    IF (u.u_name IS NULL, '未分配', u.u_name) AS u_name,  ")
                .append("  ").append("   IF (u.id IS NULL, '未分配', u.id) AS u_id  ").append("   FROM  ")
    /**
     * 2017年10月11日10:29:13
     * 修改代码 任务数改为车辆数（车辆去重）
      */
                .append(" ( SELECT * FROM t_crm_car_workorder WHERE company_id = ? AND FROM_UNIXTIME( balance_date / 1000, '%Y-%m' ) = ? " +
                        " AND wx_type IN ('首保', '二保', '定保') GROUP BY vin   ) o  ");
        params.add(companyId);
        params.add(baseMon);

                sqlBuf.append(" LEFT  JOIN t_crm_car_conf_detail dl ON o.vin = dl.vin  ")
                .append("   AND dl.role_id = '" + SA_ROLE_ID + "'  ")
                .append("   AND o.company_id = dl.company_id  ").append("   LEFT JOIN t_bc_user u ON dl.u_id = u.id  ")
                .append("   WHERE  ").append("    o.company_id = ? ");
        params.add(companyId);
        if (!TokenUtil.isCompanyAdmin(uid) && curCompanyId.equals(companyId)) {
            sqlBuf.append(" AND dl.u_id = ? ");
            params.add(uid);
        } else {
            if (!StringUtils.isEmpty(id) && !"合计".equals(id)) {
                sqlBuf.append(" AND dl.u_id = ? ");
                params.add(id);
            }
        }

        sqlBuf.append("   AND FROM_UNIXTIME(o.balance_date / 1000, '%Y-%m') = ? ");
        params.add(baseMon);


        sqlBuf.append("   AND o.wx_type IN ('首保', '二保', '定保') ")
                .append("   ) temp ");


        switch (type) {
            case "sb": {
                sqlBuf.append(" where temp.wx_type = '首保' ");
            }
            break;
            case "sbhc": {

                sqlBuf.append(" where temp.wx_type = '首保' and temp.next_date is not null");
            }
            break;
            case "eb": {

                sqlBuf.append(" where temp.wx_type = '二保' ");
            }
            break;
            case "ebhc": {

                sqlBuf.append(" where temp.wx_type = '二保' and temp.next_date is not null");
            }
            break;
            case "db": {
                sqlBuf.append(" where temp.wx_type = '定保' ");
            }
            break;
            case "dbhc": {
                sqlBuf.append(" where temp.wx_type = '定保' and temp.next_date is not null");
            }
            break;
            case "total":
                break;
            case "totalhc": {

                sqlBuf.append(" where temp.next_date is not null");
            }
            break;

        }
        sqlBuf.append(" )   temp2 ");
        sqlBuf.append("  LEFT JOIN t_crm_car c ON temp2.vin = c.vin ")
                .append("  AND temp2.company_id = c.company_id  ");
//
        return this.iRealMaintainBackDao.page(sqlBuf.toString(), params.toArray(), start, limit);
    }

    /**
     * 基盘月时间按销售日期
     * 考核期指定回厂的截止日期，默认为当天，在期间内回厂首保的统计
     * 保回站率
     *
     * @param baseMon
     * @param mDate
     * @param companyId
     * @return
     * @throws Exception
     */
    @Override
    public List sbBack(String baseMon, String mDate, String companyId) throws Exception {
        // 基盘月 2017-03
        if (StringUtils.isEmpty(baseMon)) {
            Long todayL = DateUtils.getNowTime();
            Long preSixmL = DateUtils.addMonthLongTime(todayL, -6);
            baseMon = DateUtils.formatLongTime(preSixmL, "YYYY-MM");
        }
        //考核期
        if (StringUtils.isEmpty(mDate)) {
            mDate = DateUtils.getTimeStr("YYYY-MM-dd");
        }

        String baseDate = baseMon + "-01";
        List params = new ArrayList();

        StringBuffer sql = new StringBuffer();

        sql.append(" SELECT ")
                .append("  temp.company_id,")
                .append("         count(temp.vin) AS sb,")
                .append("  count(temp.order_num) AS sbhc,")

                .append("   IF (")
                .append("        count(temp.vin) = 0,")
                .append("         0,")
                .append("         round(")
                .append("                  (count(temp.order_num) / count(temp.vin))*100,")
                .append("                2")
                .append("          )")
                .append(" ) AS lv")
                .append(" FROM ")
                .append("     ( ")
                .append("            SELECT ")
                .append("           c.company_Id, ")
                .append("           c.vin, ")
                .append("          o.order_num ")
                .append("           FROM ")
                .append("            t_crm_car c ")
                .append("           LEFT JOIN t_crm_car_workorder o ON o.wx_type = '首保'  ")
                .append("           AND c.company_id = o.company_id  ")
                .append("           AND c.vin = o.vin ")
                .append("            AND FROM_UNIXTIME(o.balance_date / 1000, '%Y-%m-%d') BETWEEN ? ")
                .append("    AND ? ")
                .append("     WHERE ")
                .append("     FROM_UNIXTIME(c.buy_date / 1000, '%Y-%m') = ? ")
                .append("      AND c.company_id = ? ")
                .append("    ) temp ");

        params.add(baseDate);
        params.add(mDate);
        params.add(baseMon);
        params.add(companyId);
        return this.iRealMaintainBackDao.list(sql.toString(), params.toArray());
    }

    /**
     * 基盘月时间按销售日期
     * 考核期指定回厂的截止日期，默认为当天，在期间内回厂首保的统计
     * 首保回站明细
     *
     * @param baseMon
     * @param mDate
     * @param companyId
     * @return
     * @throws Exception
     */
    @Override
    public Map sbBackDetail(String baseMon, String mDate, String companyId, String id, String type, String start, String limit) throws Exception {
        // 基盘月 2017-03
        if (StringUtils.isEmpty(baseMon)) {
            Long todayL = DateUtils.getNowTime();
            Long preSixmL = DateUtils.addMonthLongTime(todayL, -6);
            baseMon = DateUtils.formatLongTime(preSixmL, "YYYY-MM");
        }
        //考核期
        if (StringUtils.isEmpty(mDate)) {
            mDate = DateUtils.getTimeStr("YYYY-MM-dd");
        }

        String baseDate = baseMon + "-01";
        List params = new ArrayList();

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT temp.* ")
                .append(" FROM ")
                .append("     ( ")
                .append("            SELECT ")
                .append("           c.*,o.order_num ")
                .append("           FROM ")
                .append("            t_crm_car c ")
                .append("           LEFT JOIN t_crm_car_workorder o ON o.wx_type = '首保'  ")
                .append("           AND c.company_id = o.company_id  ")
                .append("           AND c.vin = o.vin ")
                .append("            AND FROM_UNIXTIME(o.balance_date / 1000, '%Y-%m-%d') BETWEEN ? ")
                .append("    AND ? ")
                .append("     WHERE ")
                .append("     FROM_UNIXTIME(c.buy_date / 1000, '%Y-%m') = ? ")
                .append("      AND c.company_id = ? ");
        //计算回厂明细
        if (type.equals("sbhc")) {
            sql.append(" AND    o.order_num is not null ");
        }
        sql.append("   order by c.buy_date asc ) temp ");


        params.add(baseDate);
        params.add(mDate);
        params.add(baseMon);
        params.add(companyId);

        return this.iRealMaintainBackDao.page(sql.toString(), params.toArray(), start, limit);
    }

    public Boolean appendWhereIfNeed(StringBuffer sql, Boolean hasWhere) {

        if (hasWhere == false) {

            sql.append(" where ");

        } else {

            sql.append(" and ");

        }
        return true;

    }
}
